{
 "metadata": {
  "name": "",
  "signature": "sha256:2b4d05fc46b221646ad883b29db64eb2270219a3aa1fc8d6f84b2e34e88fda3e"
 },
 "nbformat": 3,
 "nbformat_minor": 0,
 "worksheets": [
  {
   "cells": [
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Dev for a spatialite P190 database"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "from rockfish2.utils.loaders import get_example_file\n",
      "from rockfish2.navigation.p190 import P190"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 12
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "p190 = P190(filename=get_example_file('MGL1407MCS15.TEST.p190'), database='temp.p190.sqlite')"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stderr",
       "text": [
        "[2014-10-22 14:43] INFO: rockfish: Creating new database: temp.p190.sqlite\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stderr",
       "text": [
        "[2014-10-22 14:43] INFO: rockfish: Creating new database: temp.p190.sqlite\n"
       ]
      },
      {
       "output_type": "stream",
       "stream": "stderr",
       "text": [
        "[2014-10-22 14:43] INFO: rockfish: Reading p190 data from: /Users/ncm/Dev/Rockfish2/rockfish2/navigation/p190/tests/data/MGL1407MCS15.TEST.p190\n"
       ]
      }
     ],
     "prompt_number": 13
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "p190._init_spatial()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 14
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "How to create geometry table and add data"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "sql = \"\"\"DROP TABLE IF EXISTS test_geom\"\"\"\n",
      "p190.execute(sql)\n",
      "\n",
      "sql = \"\"\"CREATE TABLE test_geom (\n",
      "  id INTEGER NOT NULL\n",
      "    PRIMARY KEY AUTOINCREMENT,\n",
      "  name TEXT NOT NULL,\n",
      "  easting DOUBLE NOT NULL,\n",
      "  northing DOUBLE NOT NULL);\"\"\"\n",
      "p190.execute(sql)\n",
      "\n",
      "sql = \"\"\"SELECT AddGeometryColumn('test_geom', 'geom',\n",
      "  4326, 'POINT', 'XY');\"\"\"\n",
      "p190.execute(sql)\n",
      "\n",
      "sql = \"\"\"\n",
      "INSERT INTO test_geom\n",
      "    (id, name,  easting, northing, geom)\n",
      "  VALUES (NULL, 'first point', 1.01, 2.02,\n",
      "    GeomFromText('POINT(1.01 2.02)', 4326));\"\"\"\n",
      "\n",
      "p190.execute(sql)\n",
      "p190.commit()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [],
     "prompt_number": 15
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# add new point in two steps\n",
      "sql = \"\"\"INSERT INTO test_geom\n",
      "    (id, name, easting, northing)\n",
      "    VALUES (NULL, 'second point', 2., 3.)\"\"\"\n",
      "\n",
      "print sql\n",
      "\n",
      "p190.execute(sql)\n",
      "p190.commit()\n",
      "\n",
      "\n",
      "\n"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "INSERT INTO test_geom\n",
        "    (id, name, easting, northing)\n",
        "    VALUES (NULL, 'second point', 13., 923.)\n"
       ]
      }
     ],
     "prompt_number": 28
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "sql = \"SELECT easting, northing, rowid FROM test_geom\"\n",
      "dat = p190.read_sql(sql)\n",
      "\n",
      "i = 1\n",
      "sql = \"UPDATE test_geom SET geom=GeomFromText('POINT(? ?)', 4326) WHERE id=?\"\n",
      "\n",
      "print sql\n",
      "\n",
      "p190.executemany(sql, np.asarray(dat))\n",
      "p190.commit()"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "output_type": "stream",
       "stream": "stdout",
       "text": [
        "UPDATE test_geom SET geom=GeomFromText('POINT(? ?)', 4326) WHERE id=?\n"
       ]
      },
      {
       "ename": "P190DatabaseError",
       "evalue": "executemany() failed with 'DatabaseError: executemany() failed with 'ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 3 supplied.' while executing: UPDATE test_geom SET geom=GeomFromText('POINT(? ?)', 4326) WHERE id=?' while executing: UPDATE test_geom SET geom=GeomFromText('POINT(? ?)', 4326) WHERE id=?",
       "output_type": "pyerr",
       "traceback": [
        "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m\n\u001b[0;31mP190DatabaseError\u001b[0m                         Traceback (most recent call last)",
        "\u001b[0;32m<ipython-input-35-c6e56d5caf1a>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      7\u001b[0m \u001b[0;32mprint\u001b[0m \u001b[0msql\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      8\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 9\u001b[0;31m \u001b[0mp190\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecutemany\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msql\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mnp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0masarray\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdat\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     10\u001b[0m \u001b[0mp190\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mcommit\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
        "\u001b[0;32m/Users/ncm/Dev/Rockfish2/rockfish2/navigation/p190/database.pyc\u001b[0m in \u001b[0;36mexecutemany\u001b[0;34m(self, *args)\u001b[0m\n\u001b[1;32m    206\u001b[0m             \u001b[0mmsg\u001b[0m \u001b[0;34m+=\u001b[0m \u001b[0;34m' while executing: {:}'\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    207\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 208\u001b[0;31m             \u001b[0m_process_exception\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmsg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    209\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    210\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0minsert\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtable\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
        "\u001b[0;32m/Users/ncm/Dev/Rockfish2/rockfish2/navigation/p190/database.pyc\u001b[0m in \u001b[0;36m_process_exception\u001b[0;34m(exception, message)\u001b[0m\n\u001b[1;32m     99\u001b[0m         \u001b[0;32mraise\u001b[0m \u001b[0mP190DatabaseIntegrityError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmessage\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    100\u001b[0m     \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 101\u001b[0;31m         \u001b[0;32mraise\u001b[0m \u001b[0mP190DatabaseError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmessage\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    102\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    103\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
        "\u001b[0;31mP190DatabaseError\u001b[0m: executemany() failed with 'DatabaseError: executemany() failed with 'ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 3 supplied.' while executing: UPDATE test_geom SET geom=GeomFromText('POINT(? ?)', 4326) WHERE id=?' while executing: UPDATE test_geom SET geom=GeomFromText('POINT(? ?)', 4326) WHERE id=?"
       ]
      }
     ],
     "prompt_number": 35
    },
    {
     "cell_type": "heading",
     "level": 2,
     "metadata": {},
     "source": [
      "Adding geometry to p190 tables"
     ]
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# add geometry column to coordinates table\n",
      "p190.EPSG = 32619\n",
      "\n",
      "sql = \"SELECT addGeometryColumn('{:}', 'geom', {:}, 'POINT', 'XY');\"\\\n",
      "    .format(p190.COORDINATE_TABLE,  p190.EPSG)\n",
      "    \n",
      "p190.execute(sql)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "metadata": {},
       "output_type": "pyout",
       "prompt_number": 5,
       "text": [
        "<pysqlite2.dbapi2.Cursor at 0x1126c4490>"
       ]
      }
     ],
     "prompt_number": 5
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "# add data\n",
      "sql = \"INSERT INTO {:}(geom) VALUES (GeomFromText('POINT(? ?)', {:}))\"\\\n",
      "    .format(p190.COORDINATE_TABLE, p190.EPSG)\n",
      "\n",
      "dat = p190.read_sql('SELECT easting, northing FROM {:}'.format(p190.COORDINATE_TABLE))\n",
      "\n",
      "p190.executemany(sql, np.asarray(dat))"
     ],
     "language": "python",
     "metadata": {},
     "outputs": [
      {
       "ename": "P190DatabaseError",
       "evalue": "executemany() failed with 'DatabaseError: executemany() failed with 'ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 2 supplied.' while executing: INSERT INTO coordinates(geom) VALUES (GeomFromText('POINT(? ?)', 32619))' while executing: INSERT INTO coordinates(geom) VALUES (GeomFromText('POINT(? ?)', 32619))",
       "output_type": "pyerr",
       "traceback": [
        "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m\n\u001b[0;31mP190DatabaseError\u001b[0m                         Traceback (most recent call last)",
        "\u001b[0;32m<ipython-input-6-c31410a0f128>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      4\u001b[0m \u001b[0mdat\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mp190\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mread_sql\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'SELECT easting, northing FROM {:}'\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mp190\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mCOORDINATE_TABLE\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      5\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 6\u001b[0;31m \u001b[0mp190\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mexecutemany\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0msql\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mnp\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0masarray\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdat\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
        "\u001b[0;32m/Users/ncm/Dev/Rockfish2/rockfish2/navigation/p190/database.pyc\u001b[0m in \u001b[0;36mexecutemany\u001b[0;34m(self, *args)\u001b[0m\n\u001b[1;32m    206\u001b[0m             \u001b[0mmsg\u001b[0m \u001b[0;34m+=\u001b[0m \u001b[0;34m' while executing: {:}'\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mformat\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    207\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 208\u001b[0;31m             \u001b[0m_process_exception\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mmsg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    209\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    210\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0minsert\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mself\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtable\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
        "\u001b[0;32m/Users/ncm/Dev/Rockfish2/rockfish2/navigation/p190/database.pyc\u001b[0m in \u001b[0;36m_process_exception\u001b[0;34m(exception, message)\u001b[0m\n\u001b[1;32m     99\u001b[0m         \u001b[0;32mraise\u001b[0m \u001b[0mP190DatabaseIntegrityError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmessage\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    100\u001b[0m     \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 101\u001b[0;31m         \u001b[0;32mraise\u001b[0m \u001b[0mP190DatabaseError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmessage\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    102\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    103\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
        "\u001b[0;31mP190DatabaseError\u001b[0m: executemany() failed with 'DatabaseError: executemany() failed with 'ProgrammingError: Incorrect number of bindings supplied. The current statement uses 0, and there are 2 supplied.' while executing: INSERT INTO coordinates(geom) VALUES (GeomFromText('POINT(? ?)', 32619))' while executing: INSERT INTO coordinates(geom) VALUES (GeomFromText('POINT(? ?)', 32619))"
       ]
      }
     ],
     "prompt_number": 6
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "sql = \"INSERT INTO coordinates(geom) VALUES (GeomFromText('POINT(63520.2 3600513.)', 32619)) WHERE rowid=1\"\n",
      "p190.execute(sql)"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [
      "dat[0:1]"
     ],
     "language": "python",
     "metadata": {},
     "outputs": []
    },
    {
     "cell_type": "code",
     "collapsed": false,
     "input": [],
     "language": "python",
     "metadata": {},
     "outputs": []
    }
   ],
   "metadata": {}
  }
 ]
}